package com.corx.network.mysqlstorage.repository;

import com.corx.network.core.util.JdbcUtils;
import com.corx.network.core.util.JsonPathParser;
import com.corx.network.mysqlstorage.common.JdbcPagingSupport;
import com.corx.network.mysqlstorage.common.PageQuery;
import com.corx.network.mysqlstorage.common.PageResult;
import com.corx.network.mysqlstorage.common.SqlQueryBuilder;
import com.corx.network.mysqlstorage.model.DeviceDataStructured;
import com.corx.network.mysqlstorage.model.DeviceModelField;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.util.*;

/**
 * @author gx
 * @version 1.0.0
 */
public class JdbcDeviceDataStructuredRepository extends JdbcPagingSupport<DeviceDataStructured> implements DeviceDataStructuredRepository {


    public JdbcDeviceDataStructuredRepository(JdbcTemplate jdbcTemplate) {
        super(jdbcTemplate);
    }

    @Override
    protected RowMapper<DeviceDataStructured> getRowMapper() {
        return (rs, rowNum) -> {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            Map<String, Object> data = new LinkedHashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                data.put(metaData.getColumnLabel(i), rs.getObject(i));
            }
            return new DeviceDataStructured(data);
        };
    }

    @Override
    public Optional<DeviceDataStructured> findLatestOne(String tableName, Map<String, Object> model) {
        SqlQueryBuilder queryBuilder = new SqlQueryBuilder("SELECT * FROM " + tableName);
        if (model != null) {
            Set<String> keySet = model.keySet();
            for (String key : keySet) {
                if (Objects.nonNull(model.get(key))){
                    queryBuilder.whereEquals(key,model.get(key) );
                }
            }
        }
        queryBuilder.orderBy("create_time DESC");

        // 构建 SQL 和参数
        String baseSql = queryBuilder.getSql();
        Object[] params = queryBuilder.getParams();

        String paginatedSql = baseSql + " LIMIT ? OFFSET ?";

        List<Object> paramList = new ArrayList<>(Arrays.asList(params));
        paramList.add(1);
        paramList.add(0);

        List<DeviceDataStructured> list = jdbcTemplate.query(paginatedSql, getRowMapper(), paramList.toArray());

        return Optional.ofNullable(DataAccessUtils.singleResult(list));

    }

    @Override
    public PageResult<DeviceDataStructured> findByPage(String tableName, Map<String, Object> model, PageQuery pageQuery) {
        SqlQueryBuilder queryBuilder = new SqlQueryBuilder("SELECT * FROM " + tableName);
        if (model != null) {
            Set<String> keySet = model.keySet();
            for (String key : keySet) {
                if (Objects.nonNull(model.get(key))){
                    queryBuilder.whereEquals(key,model.get(key) );
                }
            }
        }
        if (pageQuery.hasSort()) {
            queryBuilder.orderBy(pageQuery.orderBy());
        }
        // 构建 SQL 和参数
        String baseSql = queryBuilder.getSql();
        Object[] params = queryBuilder.getParams();
        return super.findByPage(baseSql, params, pageQuery);
    }


    @Override
    public void delete(String tableName, Long id) {

        jdbcTemplate.update("DELETE FROM " + tableName + " WHERE id = ?", id);
    }


    @Override
    public void ensureStructuredTable(String tableName, List<DeviceModelField> fields) {
        if (!tableExists(tableName)) {
            createTable(tableName, fields);
        } else {
            ensureFieldsExist(tableName, fields);
        }
    }

    @Override
    public void insertStructuredData(String tableName, String deviceId, Long modelId, List<DeviceModelField> fields, String rawJson) {
        StringJoiner cols = new StringJoiner(",", "(", ")");
        StringJoiner placeholders = new StringJoiner(",", "(", ")");
        List<Object> values = new ArrayList<>();

        cols.add("device_id");
        cols.add("model_id");
        cols.add("create_time");
        cols.add("update_time");
        placeholders.add("?");
        placeholders.add("?");
        placeholders.add("?");
        placeholders.add("?");
        values.add(deviceId);
        values.add(modelId);
        values.add(new Date());
        values.add(new Date());

        for (DeviceModelField field : fields) {
            Object val = JsonPathParser.extractWithModel(rawJson, field.getJsonPath(), field.getFieldType(), field.getFieldName());
            cols.add(field.getFieldName());
            placeholders.add("?");
            values.add(val);
        }

        String sql = String.format("INSERT INTO %s %s VALUES %s", tableName, cols, placeholders);
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql);
            for (int i = 0; i < values.size(); i++) {
                JdbcUtils.setParameter(ps, i + 1, values.get(i));
            }
            return ps;
        });
    }

    private boolean tableExists(String tableName) {
        String sql = "SHOW TABLES LIKE ?";
        List<String> tables = jdbcTemplate.queryForList(sql, String.class, tableName);
        return !tables.isEmpty();
    }

    private void createTable(String tableName, List<DeviceModelField> fields) {
        StringBuilder sql = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
            .append(tableName)
            .append(" (")
            .append("id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT='ID',")
            .append("device_id VARCHAR(64) NOT NULL COMMENT='设备ID',")
            .append("model_id BIGINT NOT NULL COMMENT='模型ID',")
            .append("create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT='创建时间',")
            .append("update_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT='修改时间',");

        for (DeviceModelField field : fields) {
            sql.append(", ")
                .append(field.getFieldName())
                .append(" ")
                .append(JdbcUtils.toSqlType(field.getFieldType()));
        }

        sql.append(", INDEX idx_device_id (device_id)");
        sql.append(", INDEX idx_model_id (model_id)");
        sql.append(", INDEX idx_device_model (device_id, model_id)");
        sql.append(", INDEX idx_device_time (device_id, create_time)");
        sql.append(") COMMENT='设备数据动态结构表'");

        jdbcTemplate.execute(sql.toString());
    }

    private void ensureFieldsExist(String tableName, List<DeviceModelField> fields) {
        List<String> existingColumns = jdbcTemplate.query(
            "SHOW COLUMNS FROM " + tableName,
            (rs, rowNum) -> rs.getString("Field")
        );

        for (DeviceModelField field : fields) {
            if (!existingColumns.contains(field.getFieldName())) {
                String alter = String.format(
                    "ALTER TABLE %s ADD COLUMN %s %s",
                    tableName, field.getFieldName(), JdbcUtils.toSqlType(field.getFieldType())
                );
                jdbcTemplate.execute(alter);
            }
        }
    }


}
